package co.th.bjc.md.helper;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;

import co.th.bjc.md.common.CommonUtils;
import co.th.bjc.md.common.ConnectDB;
import co.th.bjc.md.model.Hospital;

public class HospitalHelper {
Logger _log = Logger.getLogger(getClass().getName());
	
	public Hospital save(Connection conn, Hospital hospital) throws Exception{
		boolean isConnNull = false;
		ConnectDB connectDB = null;
		try {
			if (conn == null){
				connectDB = new ConnectDB();
				conn = connectDB.getConnection();
				conn.setAutoCommit(true);
				isConnNull = true;
			}

			hospital = insert(conn, hospital);
		} catch (Exception ex) {
			ex.printStackTrace();
			_log.error("Save Hospital failed. Detail : " + ex.toString());
		}finally{
			if (conn != null && isConnNull){
				connectDB.close();
			}
		}
		return hospital;
	}
	
	public List<Hospital> save(List<Hospital> hospital) throws Exception{
		return null;
	}
	
	private static final String SQL_INSERT = "INSERT INTO hospital (hospital_code, hospital_name_th, hospital_name_en, hospital_type, address, telephone, email, president_code, province_id, region_id, create_by, create_date, update_by, update_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	private synchronized Hospital insert(Connection conn, Hospital hospital) throws Exception {
		PreparedStatement stmt = null;
		try {
			/* Generate HospitalCode */
			BigDecimal hospitalCode = generateHospitalCode(conn);
			_log.info("Hospital Code : " + hospitalCode);
			hospital.setHospitalCode(hospitalCode);
			
			/* Insert Person(President) */
//			if (hospital.getPresident() != null){
//				hospital.setPresident(new PersonHelper().save(conn, hospital.getPresident()));
//			}
			
			stmt = conn.prepareStatement(SQL_INSERT);
			stmt.setBigDecimal(1, hospital.getHospitalCode());
			stmt.setString(2, hospital.getHospitalNameTH());
			stmt.setString(3, hospital.getHospitalNameEN());
			stmt.setString(4, hospital.getHospitalType());
			stmt.setString(5, hospital.getAddress());
			stmt.setString(6, hospital.getTelephone());
			stmt.setString(7, hospital.getEmail());
			stmt.setBigDecimal(8, hospital.getPresident().getPersonCode());
			stmt.setBigDecimal(9, hospital.getProvince().getProvinceId());
			stmt.setBigDecimal(10, hospital.getRegion().getRegionId());
			stmt.setString(11, CommonUtils.chkStringIsNull(hospital.getCreateBy())?"SYSTEM":hospital.getCreateBy());
			stmt.setDate(12, new java.sql.Date(new Date().getTime()));
			stmt.setString(13, "SYSTEM");
			stmt.setDate(14, new java.sql.Date(new Date().getTime()));
			
			stmt.executeUpdate();
			_log.warn("Insert Hospital By HospitalCode : " + hospital.getHospitalCode());
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (stmt != null) stmt.close();
		}
		return hospital;
	}
	
	private BigDecimal generateHospitalCode(Connection conn) throws Exception{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT MAX(hospital_code) + 1 AS hospital_code FROM hospital";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			if(rs.next()){
				return rs.getBigDecimal("hospital_code");
			}
			return null;
		}catch(Exception ex){
			_log.error("generateCustomerDataID --> " + ex.toString());
			return null;
		}finally{
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
		}
	}
	
	public List<Hospital> findAllHospital() throws Exception{
		return null;
	}
	
	public List<Hospital> findHospitalByRegion(BigDecimal regionCode) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Hospital> listHospital = new ArrayList<Hospital>(0);
		ConnectDB connectDB = null;
		try{
			String sql = "SELECT * FROM hospital WHERE region_id = ?";

			_log.info("SQL : " + sql);
			connectDB = new ConnectDB();
			conn = connectDB.getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setBigDecimal(1, regionCode);
			
			rs = stmt.executeQuery();
			while (rs.next()){
				listHospital.add(this.setHospital(rs));
			}
		}catch(Exception ex){
			_log.error("findHospitalByRegion --> " + ex.toString());
			ex.printStackTrace();
		}finally{
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			connectDB.close();
		}
		return listHospital;
	}
	
	private Hospital setHospital(ResultSet rs) throws Exception{
		Hospital hospital = new Hospital();
		hospital.setHospitalCode(rs.getBigDecimal("hospital_code"));
		hospital.setHospitalNameTH(rs.getString("hospital_name_th"));
		hospital.setHospitalNameEN(rs.getString("hospital_name_en"));
		hospital.setHospitalType(rs.getString("hospital_type"));
		hospital.setAddress(rs.getString("address"));
		hospital.setTelephone(rs.getString("telephone"));
		hospital.setEmail(rs.getString("email"));
		hospital.setPresident(new PersonHelper().findPersonByPersonCode(rs.getBigDecimal("president_code")));
		hospital.setProvince(new ProvinceHelper().findProvinceByProvinceCode(rs.getBigDecimal("province_id")));
		hospital.setRegion(new RegionHelper().findRegionByRegionCode(rs.getBigDecimal("region_id")));
		hospital.setCreateBy(rs.getString("create_by"));
		hospital.setCreateDate(rs.getDate("create_date"));
		hospital.setUpdateBy(rs.getString("update_by"));
		hospital.setUpdateDate(rs.getDate("update_date"));
		return hospital;
	}
	
	public Hospital findHospitalByHospitalCode(BigDecimal hospitalCode) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Hospital hospital = null;
		ConnectDB connectDB = null;
		try{
			String sql = "SELECT * FROM hospital WHERE hospital_code = ?";
			_log.info("SQL : " + sql);
			
			connectDB = new ConnectDB();
			conn = connectDB.getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setBigDecimal(1, hospitalCode);
			
			rs = stmt.executeQuery();
			if (rs.next()){
				hospital = this.setHospital(rs);
			}
		}catch(Exception ex){
			_log.error("findHospitalByHospitalCode --> " + ex.toString());
			ex.printStackTrace();
		}finally{
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			connectDB.close();
		}
		return hospital;
	}
}
